package com.soms.dao.impl;

import com.soms.dao.UserDao;
import com.soms.entity.User;
import com.soms.util.DBUtil;
import com.soms.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {

    /**
     * 通过角色id查询相关的用户数量
     *
     * @param connection
     * @param roleId
     * @return
     */
    @Override
    public int queryCountByRoleId(Connection connection, int roleId) {
        String sql = "SELECT count(1) FROM `soms_user` where `user_role` = ?";
        Object[] params = {roleId};
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 登录校验
     *
     * @param connection
     * @param userCode
     * @param userPassword
     * @return
     */
    @Override
    public boolean doLogin(Connection connection, String userCode, String userPassword) {
        String sql = "SELECT count(1) FROM `soms_user` WHERE `user_code` = ? and `user_password` = ?";
        Object[] params = {userCode, userPassword};
        int count = 0;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return count > 0;
    }

    /**
     * 据用户编码查询用户信息
     *
     * @param connection
     * @param userCode
     * @return
     */
    @Override
    public User getUserByUserCode(Connection connection, String userCode) {
        String sql = "SELECT * FROM `soms_user` WHERE `user_code` = ?";
        Object[] params = {userCode};
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("user_code"));
                user.setUserName(resultSet.getString("user_name"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(StringUtil.transformNull(resultSet.getString("birthday")));
                user.setPhone(StringUtil.transformNull(resultSet.getString("phone")));
                user.setAddress(StringUtil.transformNull(resultSet.getString("address")));
                user.setUserRole(resultSet.getInt("user_role"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return user;
    }

    /**
     * 登录用户上传头像，保存图片的base64编码
     *
     * @param connection
     * @param userCode
     * @param avatar
     * @return
     */
    @Override
    public int uploadAvatar(Connection connection, String userCode, String avatar) {
        String sql = "UPDATE `soms_user` SET `avatar` = ? WHERE `user_code` = ?";
        Object[] params = {avatar, userCode};
        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据用户编码查询用户头像
     *
     * @param connection
     * @param userCode
     * @return
     */
    @Override
    public String getAvatar(Connection connection, String userCode) {
        String sql = "SELECT `avatar` FROM `soms_user` WHERE `user_code` = ?";
        Object[] params = {userCode};
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String avatar = null;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                avatar = resultSet.getString("avatar");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return avatar;
    }

    /**
     * 修改密码
     *
     * @param connection
     * @param userCode
     * @param userPassword
     * @return
     */
    @Override
    public int updatePassword(Connection connection, String userCode, String userPassword) {
        String sql = "UPDATE `soms_user` SET `user_password` = ? WHERE `user_code` = ?";
        Object[] params = {userPassword, userCode};
        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 判断用户名是否被其他用户使用
     *
     * @param connection
     * @param userCode
     * @param userName
     * @return
     */
    @Override
    public boolean checkUserNameExist(Connection connection, String userCode, String userName) {
        String sql = "SELECT count(1) FROM `soms_user` WHERE `user_name` = ? AND `user_code` != ?";
        Object[] params = {userName, userCode};
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        boolean exist = false;
        try {
            statement = DBUtil.getPreparedStatement(connection, sql, params);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                exist = resultSet.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(resultSet, statement);
        }
        return exist;
    }

    /**
     * 用户自己修改个人信息
     *
     * @param connection
     * @param user
     * @return
     */
    @Override
    public int updateUserBySelf(Connection connection, User user) {
        String sql = "UPDATE `soms_user` SET `user_name` = ?, `gender` = ?, `birthday` = ?, `phone` = ?, `address` = ?, `user_role` = ?, `modifier` = ?, `modified` = ? WHERE `user_code` = ?";
        Object[] params = {user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifier(), user.getModified(), user.getUserCode()};
        int result = 0;
        try {
            result = DBUtil.execute(connection, sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
